package com.linktco.common.utils.excel.handle;

import com.alibaba.fastjson.JSON;
import com.linktco.common.utils.Convert;
import com.linktco.common.utils.DateUtils;
import com.linktco.common.utils.U;
import com.linktco.common.utils.excel.SheetBean;
import com.linktco.common.utils.excel.annotation.AnalysisUtils;
import com.linktco.common.utils.excel.annotation.Excel;
import com.linktco.common.utils.excel.style.DefaultStyle;
import com.linktco.common.utils.excel.style.StylesHandler;
import com.linktco.common.utils.excel.style.WaterStyle;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.SneakyThrows;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.*;
import java.net.URLEncoder;
import java.rmi.server.ExportException;
import java.util.*;
import java.util.stream.Collectors;


/**
 * 执行导出操作
 *
 * @author zhangnx
 */
@ApiModel("导出工具")
public class ExportExcel<T> {
    private final static Logger log = LoggerFactory.getLogger(ExportExcel.class);

    @ApiModelProperty("实体对象")
    private Class<T> clazz;

    @ApiModelProperty("导出数据列表")
    private List<T> exportDataList;

    @ApiModelProperty("工作表名称")
    private String sheetName;

    @ApiModelProperty("文件名称")
    private String fileName;

    @ApiModelProperty("Excel注解列表")
    private List<Object[]> fields;

    @ApiModelProperty("Excel sheet最大行数，如果数据量过大就生成多个sheet")
    private final int sheetSize = 100000;

    @ApiModelProperty("工作薄对象")
    private Workbook wb;

    @ApiModelProperty("工作表对象")
    private Sheet sheet;

    @ApiModelProperty("样式列表")
    private Map<String, CellStyle> styles;

    @ApiModelProperty("统计列表")
    private Map<String, Double> statistics = new LinkedHashMap<>();

    @ApiModelProperty("自定义样式接口")
    private StylesHandler stylesHandler;

    @ApiModelProperty("多个sheet导出Bean")
    private List<SheetBean> sheetBeanList;

    @ApiModelProperty("导出Handler")
    private ExportHandler exportHandler;

    @ApiModelProperty("分页导出时分页大小")
    private int pageSize;


    private Integer exportTotalRow = 0;


    public ExportExcel(Class<T> clazz, List<T> dataList, String sheetName, String fileName, StylesHandler stylesHandler, List<String> exportFields) {
        this.clazz = clazz;
        this.exportDataList = dataList == null ? new ArrayList<>() : dataList;
        this.sheetName = U.isEmpty(sheetName) ? "Sheet" : sheetName;
        this.fileName = this.encodingFileName(fileName);
        this.wb = new SXSSFWorkbook();
        //得到所有要导出的字段
        this.createExcelField(exportFields);
        this.stylesHandler = stylesHandler;
    }

    public ExportExcel(List<SheetBean> sheetBeanList, String fileName, StylesHandler stylesHandler) {
        this.sheetBeanList = sheetBeanList;
        this.fileName = this.encodingFileName(fileName);
        this.wb = new SXSSFWorkbook();
        this.stylesHandler = stylesHandler;
    }


    public ExportExcel(String fileName, String sheetName, ExportHandler exportHandler, int pageSize, Class<T> clazz) {
        this.clazz = clazz;
        this.fileName = this.encodingFileName(fileName);
        this.wb = new SXSSFWorkbook();
        this.exportHandler = exportHandler;
        this.pageSize = pageSize;
        this.sheetName = U.isEmpty(sheetName) ? "Sheet" : sheetName;
    }






    public MultipartFile export() {
        MultipartFile multipartFile = null;
        OutputStream out = null;
        Workbook workbook = null;
        try {
            workbook = this.execute();
            FileItemFactory factory = new DiskFileItemFactory(16, null);
            FileItem fileItem = factory.createItem("textField", "text/plain", true, fileName + ".xlsx");
            out = fileItem.getOutputStream();
            workbook.write(out);
            out.flush();
            multipartFile = new CommonsMultipartFile(fileItem);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            closeStream(workbook, out);
        }
        return multipartFile;

    }




    /**
     * 执行导出操作
     */
    public void export(HttpServletResponse response) {

        OutputStream out = null;
        Workbook workbook = null;
        try {
            workbook = execute();
            if (response==null){
                File file = new File("F://excel/");
                if (!file.exists()){file.mkdirs();}
                String savePath = String.format("%s/%s.xls",file.getPath(), DateUtils.format(new Date(), DateUtils.YMDHMS));
                OutputStream os = new FileOutputStream(savePath);
                workbook.write(os);
                os.flush();
                os.close();
            }else {
                response.setHeader("Content-disposition", "attachment; filename=" + this.fileName);
                out = response.getOutputStream();
                log.debug("==> 正在写入到响应流中...");
                workbook.write(out);
                out.flush();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            closeStream(workbook, out);
        }
    }




    /**
     * 执行导出操作
     */
    public Workbook execute() {
        long startTime = System.currentTimeMillis();
        log.info("==> 开始执行导出操作...");
        try {

            if (exportHandler != null) {
                log.info("分页导出...");
                this.pageExport();

            } else if (U.notEmpty(sheetBeanList)) {
                log.info("多Sheet导出...");
                this.multipleSheetExport();

            } else {
                log.info("单Sheet导出...");
                this.singleSheetExport();
            }

            long endTime = System.currentTimeMillis();
            log.info("==> 导出{}条数据执行完成,总耗时{}秒...", exportTotalRow, (endTime - startTime) / 1000);

        } catch (Exception e) {
            e.printStackTrace();
        }

        return this.wb;
    }






    private void singleSheetExport() throws Exception {
        double ceil = Math.ceil(this.exportDataList.size() / this.sheetSize);
        log.debug("==> 预计创建{}个sheet...", ceil);
        int sheetNo = new Double(ceil).intValue();
        for (int i = 0; i <= sheetNo; i++) {
            this.createSheet(sheetNo, i);
            int titleLineNum = this.createTitle();
            if (getChildrenListField(clazz) != null) {
                this.fillDataMergeExcel(i, titleLineNum);
            } else {
                this.fillExcelData(i, titleLineNum);
            }
            this.addStatisticsRow();
        }
    }


    private void multipleSheetExport() throws Exception {
        int createSheetIndex = 0;
        for (SheetBean sheetBean : sheetBeanList) {
            this.clazz = sheetBean.getClazz();
            this.exportDataList = sheetBean.getDataList();
            this.sheetName = sheetBean.getSheetName();
            double ceil = Math.ceil(this.exportDataList.size() / this.sheetSize);
            sheetFillExcel(createSheetIndex, ceil);
            createSheetIndex += ceil + 1;
            this.addStatisticsRow();
        }
    }


    /**
     * 分页导出
     *
     * @throws Exception
     */
    private void pageExport() throws Exception {

        int sheetNo = 0, writeLineNum = 0, currentPage = 0;
        while (true) {

            boolean isCreatSheet = sheetSize <= writeLineNum && writeLineNum <= sheetSize + pageSize;
            if (sheetNo == 0 || isCreatSheet) {
                this.createExcelField(null);
                this.createSheet(sheetNo, sheetNo);
                sheetNo++;
                writeLineNum = this.createTitle();
            }

            List<T> dataList = exportHandler.getDataList(++currentPage, pageSize);
            if (U.isEmpty(dataList)){
                break;
            }
            for (T vo : dataList) {
                exportTotalRow++;
                Row row = this.sheet.createRow(writeLineNum++);
                this.fillColumn(row, vo);
            }
            // 合计统计信息
            this.addStatisticsRow();

        }
    }






    /**
     * 得到所有要导出的字段（加注解的字段）
     *
     * @param exportFields
     */
    private void createExcelField(List<String> exportFields) {
        this.fields = new ArrayList<>();
        List<Field> tempFields = new ArrayList<>();
        tempFields.addAll(Arrays.asList(this.clazz.getSuperclass().getDeclaredFields()));
        tempFields.addAll(Arrays.asList(this.clazz.getDeclaredFields()));
        this.recursionFieldToList(tempFields, "", exportFields);
        this.fields = this.fields.stream().sorted(Comparator.comparing(field -> ((Excel) field[1]).sort())).collect(Collectors.toList());
        this.sortField();
    }






    /**
     * 递归把需要导出的属性都放到集合中
     *
     * @param tempFields   所有的属性集合
     * @param parentTitles 父标题,多层之间用^隔开
     * @param exportFields
     */
    private void recursionFieldToList(List<Field> tempFields, String parentTitles, List<String> exportFields) {
        for (Field field : tempFields) {
            //判断属性有没有加Excel注解
            if (field.isAnnotationPresent(Excel.class)) {
                Excel attr = field.getAnnotation(Excel.class);

                if (U.notEmpty(exportFields)) {
                    //把动态导出的属性先按策略筛选再放到fields集合里
                    if (exportFields.contains(field.getName())) {
                        this.recursionAddField(attr, field, parentTitles, exportFields);
                    }
                } else {
                    this.recursionAddField(attr, field, parentTitles, exportFields);
                }
            }
        }
    }





    private void recursionAddField(Excel attr, Field field, String parentTitles, List<String> exportFields) {
        if (attr.hasChildren()) {
            try {
                Class<?> aClass = field.getType();
                //获取子级的所有属性
                Field[] declaredFields = aClass.getDeclaredFields();
                //把加了注解的属性都放到集合中
                recursionFieldToList(Arrays.asList(declaredFields), parentTitles + "^" + this.getTitleText(attr, field), exportFields);
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else if (attr.hasChildrenList()) {
            //如果属性有子级就继续扫描子级的属性
            try {
                Type t = field.getGenericType();
                if (t instanceof ParameterizedType) {
                    ParameterizedType pt = (ParameterizedType) t;
                    Class<?> clz = (Class) pt.getActualTypeArguments()[0];
                    Field[] declaredFields = clz.getDeclaredFields();
                    recursionFieldToList(Arrays.asList(declaredFields), parentTitles + "^" + this.getTitleText(attr, field), exportFields);
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        } else {
            this.fields.add(new Object[]{field, attr, StringUtils.isNotEmpty(parentTitles) ? parentTitles.substring(1) + "^" + this.getTitleText(attr, field) : this.getTitleText(attr, field)});
        }
    }






    /**
     * 排序
     */
    private void sortField() {
        Collections.sort(this.fields, (prior, after) -> {
            Excel priorAttr = (Excel) prior[1];
            Excel afterAttr = (Excel) after[1];
            return priorAttr.sort() - afterAttr.sort();
        });
    }






    /**
     * 编码文件名
     *
     * @param fileName 文件名
     * @return fileName
     */
    private String encodingFileName(String fileName) {
        if (StringUtils.isNotEmpty(fileName)) {
            if (fileName.lastIndexOf(".") != -1) {
                String suffix = fileName.substring(fileName.lastIndexOf("."));
                if (!".xlsx".equals(suffix.toLowerCase()) && !".xls".equals(suffix.toLowerCase())) {
                    fileName = fileName + ".xlsx";
                }
            } else {
                fileName = fileName + ".xlsx";
            }
        } else {
            fileName = this.sheetName + ".xlsx";
        }
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return fileName;
    }







    /**
     * 创建sheet,数据量大就创建多个sheet
     *
     * @param sheetNo
     * @param index
     */
    private void createSheet(double sheetNo, int index) {
        this.sheet = this.wb.createSheet();

        Excel clazzAttr = this.clazz.getAnnotation(Excel.class);
        if (U.notEmpty(clazzAttr)) {
            if (U.notEmpty(clazzAttr.sheetPwd())) {
                //添加密码保护
                this.sheet.protectSheet(clazzAttr.sheetPwd());
            }
            if (U.notEmpty(this.exportDataList)) {
                String waterText = clazzAttr.waterText();
                if (U.notEmpty(waterText)) {
                    //添加水印
                    BufferedImage watermarkImage = WaterStyle.createWatermarkImage(waterText, clazzAttr.waterColor(), clazzAttr.waterSize());
                    WaterStyle.putWaterRemarkToExcel(this.wb, this.sheet, watermarkImage, this.exportDataList.size(), this.fields.size(), clazzAttr.waterDensity());
                }
            }
        }
        this.styles = this.createStyles();
        if (sheetNo == 0) {
            this.wb.setSheetName(index, this.sheetName);
        } else {
            this.wb.setSheetName(index, this.sheetName + index);
        }
    }






    /**
     * 创建表格样式
     *
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles() {
        CellStyle headerStyle = DefaultStyle.headerStyle(wb.createCellStyle(), wb.createFont());
        CellStyle dataStyle = DefaultStyle.dataStyle(wb.createCellStyle(), wb.createFont());
        CellStyle totalStyle = DefaultStyle.totalStyle(wb.createCellStyle(), wb.createFont());

        if (this.stylesHandler != null) {
            headerStyle = stylesHandler.headerStyles(headerStyle, wb.createFont());
            dataStyle = stylesHandler.dataStyles(dataStyle, wb.createFont());
            totalStyle = stylesHandler.totalStyles(totalStyle, wb.createFont());
        }

        Map<String, CellStyle> styles = new HashMap<>(3);
        styles.put("header", headerStyle);
        styles.put("data", dataStyle);
        styles.put("total", totalStyle);
        return styles;

    }






    /**
     * 得到标题文本
     *
     * @param attr  属性上的注解对象
     * @param field 属性对象
     * @return
     */
    private String getTitleText(Excel attr, Field field) {
        if (StringUtils.isNotEmpty(attr.name())) {
            return attr.name();
        } else {
            return field.getName();
        }
    }






    /**
     * 得到某类中加了注解并可进入的子属性
     *
     * @param clazz
     * @return
     */
    private List<Field> getEnterAbleField(Class<?> clazz) {
        List<Field> fieldList = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(Excel.class)) {
                Excel attr = field.getAnnotation(Excel.class);
                if (attr.hasChildren() || attr.hasChildrenList()) {
                    fieldList.add(field);
                }
            }
        }
        return fieldList;
    }







    /**
     * 创建文件内容的标题
     */
    private int createTitle() {
        //定义标题行数，默认1行
        int titleRowNum = 1;
        //如果类上加了注解就创建一行总标题
        String totalTitle = null;
        if (this.clazz.isAnnotationPresent(Excel.class)) {
            Excel clazzAnnotation = this.clazz.getAnnotation(Excel.class);
            totalTitle = clazzAnnotation.name();
        }

        List<String> titles = new ArrayList<>();
        List<String> normTitles = new ArrayList<>();
        Map<String, Integer> tempMap = new HashMap<>(5);
        for (Object[] obj : this.fields) {
            if (totalTitle != null) {
                String[] split = obj[2].toString().split("\\^");
                List<String> list = Arrays.asList(split);
                if (!list.contains(totalTitle)) {
                    obj[2] = totalTitle + "^" + obj[2];
                }
            }
            String title = (String) obj[2];

            String[] temp = title.split("\\^");
            //取最大长度值
            if (temp.length > titleRowNum) {
                titleRowNum = temp.length;
            }
            normTitles.add(title);
            titles.addAll(statisticalGroup(temp));
        }


        //如果只有一行标题，不需要分析合并的事
        if (titleRowNum == 1) {
            Row row = this.sheet.createRow(0);
            int count = 0;
            for (String title : normTitles) {
                Excel attr = (Excel) this.fields.get(count)[1];
                //设置单元格固定值
                if (attr.combo().length > 0) {
                    this.setCellFixedValue(this.sheet, attr.combo(), titleRowNum, this.sheetSize - 1, count, count);
                }
                // 设置列宽
                sheet.setColumnWidth(count, (int) (attr.width() * 256));
                // 设置行高
                row.setHeight((short) (attr.titleHeight() * 20));
                Cell cell = row.createCell(count++);
                cell.setCellStyle(this.styles.get("header"));
                cell.setCellValue(title);
            }
            return titleRowNum;
        }

        //统计每个标题出现的次数(便于横向合并)
        for (String title : titles) {
            if (tempMap.containsKey(title)) {
                int count = tempMap.get(title);
                tempMap.put(title, ++count);
            } else {
                tempMap.put(title, 1);
            }
        }

        //创建复杂表头
        return createComplexTitle(titleRowNum, tempMap);
    }






    /**
     * 创建复杂表头
     *
     * @param titleRowNum
     * @param tempMap
     * @return
     */
    private Integer createComplexTitle(int titleRowNum, Map<String, Integer> tempMap) {
        //循环创建每行标题
        List<Integer> lastRowWantVerticalMergerList = new ArrayList<>();
        for (int i = 0; i < titleRowNum; i++) {
            Row row = this.sheet.createRow(i);
            for (int j = 0; j < this.fields.size(); ) {
                Object[] obj = this.fields.get(j);
                Excel attr = (Excel) obj[1];
                String title = (String) obj[2];

                // 设置行高
                row.setHeight((short) (attr.titleHeight() * 20));
                // 设置列宽
                sheet.setColumnWidth(j, (int) (attr.width() * 256));

                String[] temp = title.split("\\^");
                int titleLength = temp.length;

                //如果循环到的此列没有被之前行纵向合并就给此列填充内容
                if (!lastRowWantVerticalMergerList.contains(j)) {
                    Cell cell = row.createCell(j);

                    //设置单元格固定值
                    if (attr.combo().length > 0) {
                        this.setCellFixedValue(this.sheet, attr.combo(), titleRowNum, this.sheetSize - 1, j, j);
                    }

                    CellRangeAddress region = null;
                    if (titleLength == 1) {
                        region = new CellRangeAddress(i, titleRowNum - 1, j, j);
                        lastRowWantVerticalMergerList.add(j++);
                        cell.setCellValue(title);
                    } else {
                        //创建哪一行就取哪个下标的标题
                        String currentTitle = getCurrentTitle(temp, i);
                        String currentSimpleTitle = temp[i];
                        //如果当前行不是数组末尾下标就横向合并
                        if (titleLength - 1 > i) {
                            region = new CellRangeAddress(i, i, j, j + tempMap.get(currentTitle) - 1);
                            for (int c = j + 1; c < j + tempMap.get(currentTitle); c++) {
                                Cell nullCell = row.createCell(c);
                                nullCell.setCellStyle(this.styles.get("header"));
                            }
                            j += tempMap.get(currentTitle);
                            //如果当前行在数组末尾就纵向合并到标题最后一行
                        } else if (titleLength - 1 == i && titleLength != titleRowNum) {
                            region = new CellRangeAddress(i, titleRowNum - 1, j, j);
                            lastRowWantVerticalMergerList.add(j++);
                        } else {
                            j++;
                        }
                        cell.setCellValue(currentSimpleTitle);
                    }
                    if (region != null) {
                        try {
                            if (tempMap.size() > titleRowNum) {
                                //addMergedRegion安全耗时
                                this.sheet.addMergedRegionUnsafe(region);
                            }
                        } catch (IllegalStateException e) {
                            e.printStackTrace();
                            throw new RuntimeException("创建复杂标题出错了，子级内的属性顺序必须相邻。");
                        }
                    }
                    cell.setCellStyle(this.styles.get("header"));
                } else {
                    Cell cell = row.createCell(j++);
                    cell.setCellStyle(this.styles.get("header"));
                }
            }
        }
        return titleRowNum;
    }







    /**
     * 返回标题的阶梯值集合
     *
     * @param array
     * @return
     */
    public static List<String> statisticalGroup(String[] array) {
        List<String> list = new ArrayList<String>();
        for (int i = 0; i < array.length; i++) {
            String str = "";
            for (int j = 0; j <= i; j++) {
                str += "^" + array[j];
            }
            list.add(str.substring(1));
        }
        return list;
    }






    /**
     * 获取当前标题
     *
     * @param array
     * @param index
     * @return
     */
    public static String getCurrentTitle(String[] array, int index) {
        String str = "";
        for (int i = 0; i <= index; i++) {
            str += "^" + array[i];
        }
        return str.substring(1);
    }






    /**
     * 设置单元格的固定值
     *
     * @param sheet    sheet对象
     * @param textList 下拉框显示的文本数组
     * @param firstRow 开始行
     * @param endRow   结束行
     * @param firstCol 开始列
     * @param endCol   结束列
     */
    private void setCellFixedValue(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }







    /**
     * 递归找到当前属性所属的对象
     *
     * @param field 当前列所代表的属性
     * @param o     可能存在的对象
     * @return
     */
    private T recursionGetFieldType(Field field, T o) throws ExportException {
        if (U.isEmpty(o)) {
            return o;
        }
        Class<? extends Object> clazz = o.getClass();
        Field[] fields = clazz.getDeclaredFields();
        if (Arrays.asList(fields).contains(field)) {
            return o;
        }
        List<Field> enterAbleFieldList = this.getEnterAbleField(clazz);
        for (Field childField : enterAbleFieldList) {
            String fieldName = childField.getName();
            String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
            try {
                Method method = clazz.getMethod(methodName, null);
                T t = (T) method.invoke(o);
                return recursionGetFieldType(field, t);
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
                throw new ExportException("没有找到属性“" + field.getName() + "”的get方法。");
            } catch (IllegalAccessException e) {
                e.printStackTrace();
                throw new ExportException("属性“" + field.getName() + "”的get方法无法调用，请检查该方法的访问修饰符。");
            } catch (InvocationTargetException e) {
                e.printStackTrace();
                throw new ExportException("调用属性“" + field.getName() + "”的get方法时出错，错误原因：" + e.getMessage());
            }
        }
        throw new ExportException("没有找到属性“" + field.getName() + "”的承载对象，或承载对象为null。");
    }






    /**
     * 创建数据单元格
     *
     * @param attr   每个属性上的注解对象
     * @param vo     导出对象
     * @param field  属性对象
     * @param row    行对象
     * @param column 列数（第几列）
     * @return
     */
    private Cell createDataCell(Excel attr, T vo, Field field, Row row, int column) throws Exception {
        // 设置行高
        row.setHeight((short) (attr.height() * 20));
        // 创建cell
        Cell cell = row.createCell(column);
        //设置单元格样式
        cell.setCellStyle(this.styles.get("data"));
        // 根据Excel中设置情况决定是否导出数据,有些情况需要保持为空,希望用户填写这一列.
        if (attr.isExportData()) {
            // 获取到属性值
            field.setAccessible(true);

            Object value = null;
            if (U.notEmpty(vo)) {
                value = field.get(vo);
            } else {
                log.error("children属性值为空");
            }

            // 设置单元格信息
            this.setCellVo(vo, value, attr, cell);

            // 合计统计信息
            this.addStatisticsData(this.getTitleText(attr, field), Convert.toStr(value), attr);
        }
        return cell;
    }




    /**
     * 设置单元格信息
     *
     * @param value 单元格值
     * @param attr  注解相关
     * @param cell  单元格对象
     */
    private void setCellVo(T vo, Object value, Excel attr, Cell cell) throws Exception {
        if (U.isEmpty(attr,value)){
            return;
        }
        value = AnalysisUtils.exportConvertExp(vo, attr, value);
        if (Excel.ColumnType.STRING == attr.cellType()) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(Convert.toStr(value));
        } else {
            Double dValue = Convert.toDouble(value);
            if (dValue == null) {
                throw new ExportException("转换数据类型时发生错误，注解属性选择NUMERIC类型，但字段值无法转为double类型。");
            }
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(dValue);
        }
    }







    /**
     * 创建统计行
     */
    private void addStatisticsRow() {
        //判断如果有统计数据
        if (this.statistics.size() > 0) {
            log.debug("==> 开始填充统计数据...");
            //在最后创建一行
            Row row = this.sheet.createRow(this.sheet.getLastRowNum() + 1);
            // 设置行高
            row.setHeight((short) (((Excel) this.fields.get(0)[1]).height() * 20));
            //获取到统计数据的键
            Set<Map.Entry<String, Double>> entries = this.statistics.entrySet();
            //创建列,合并成一行
            Cell cell = row.createCell(0);
            StringBuffer sb = new StringBuffer("合计：");
            //遍历统计数据
            Iterator<Map.Entry<String, Double>> iterator = entries.iterator();
            while (iterator.hasNext()) {
                Map.Entry<String, Double> entry = iterator.next();
                sb.append("     ");
                sb.append(entry.getKey() + "：" + entry.getValue() + ",");
            }
            cell.setCellValue(sb.substring(0, sb.length() - 1));
            CellRangeAddress region = new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, this.fields.size() - 1);
            //addMergedRegion安全耗时
            this.sheet.addMergedRegionUnsafe(region);
            //设置单元格样式
            cell.setCellStyle(this.styles.get("total"));
            for (int i = 1; i < this.fields.size(); i++) {
                Cell nullCell = row.createCell(i);
                nullCell.setCellStyle(this.styles.get("total"));
            }
            //最后清空集合
            this.statistics.clear();
            log.debug("==> 统计数据填充完成...");
        }
    }





    /**
     * 合计统计信息
     *
     * @param columnName 列名
     * @param text       要统计的数据
     * @param attr       注解对象
     */
    private void addStatisticsData(String columnName, String text, Excel attr) {
        //如果注解有设置统计属性
        if (attr.isStatistics()) {
            Double temp = 0D;
            if (!this.statistics.containsKey(columnName)) {
                this.statistics.put(columnName, temp);
            }
            temp = Double.valueOf(text);
            this.statistics.put(columnName, this.statistics.get(columnName) + temp);
        }
    }




    protected void fillColumn(Row row, T vo) throws Exception {
        int column = 0;
        //遍历属性列表
        for (Object[] os : this.fields) {
            Field field = (Field) os[0];
            Excel excel = (Excel) os[1];
            // 设置实体类私有属性可访问
            field.setAccessible(true);
            //创建数据单元格，如果当前的属性属于T对象，或者T对象里面的对象属性
            if (field.getDeclaringClass().equals(vo.getClass())) {
                this.createDataCell(excel, vo, field, row, column++);
            } else {
                //递归进入找到当前属性所属的对象
                T o = this.recursionGetFieldType(field, vo);
                this.createDataCell(excel, o, field, row, column++);
            }
        }
    }



    private void closeStream(Workbook workbook, OutputStream out) {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
        if (out != null) {
            try {
                out.close();
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
    }




    private void sheetFillExcel(int createSheetIndex, double ceil) throws Exception {
        this.createExcelField(null);
        log.debug("==> 预计创建{}个sheet...", ceil);
        Integer j = 0;
        int sheetNo = new Double(ceil).intValue();
        for (int i = createSheetIndex; i <= createSheetIndex + sheetNo; i++) {

            //创建sheet,数据量大就创建多个sheet
            this.createSheet(sheetNo, i);

            //创建文件内容的标题
            int titleLineNum = this.createTitle();

            //填充excel数据
            if (getChildrenListField(clazz) != null) {
                this.fillDataMergeExcel(j, titleLineNum);
            } else {
                this.fillExcelData(j, titleLineNum);
            }

            //创建右下角统计行
            this.addStatisticsRow();
            j++;
        }
    }




    /**
     * 填充excel数据
     *
     * @param index        序号（第几份sheet）
     * @param titleLineNum 标题行数
     */
    private void fillExcelData(int index, int titleLineNum) throws Exception {
        int startNo = index * this.sheetSize;
        int endNo = Math.min(startNo + this.sheetSize, this.exportDataList.size());
        for (int i = startNo; i < endNo; i++) {

            Row row = this.sheet.createRow(titleLineNum++);
            exportTotalRow++;
            // 得到导出对象.
            T vo = this.exportDataList.get(i);

            this.fillColumn(row, vo);
        }
    }





    /**
     * 合并填充
     *
     * @param index
     * @param titleLineNum
     * @throws ExportException
     */
    private void fillDataMergeExcel(int index, int titleLineNum) throws Exception {
        int startNo = index * this.sheetSize;
        int endNo = Math.min(startNo + this.sheetSize, this.exportDataList.size());

        Field childrenListField = getChildrenListField(clazz);

        List<ExportField> sortField = getSortField(clazz);
        for (int i = startNo; i < endNo; i++) {
            T vo = this.exportDataList.get(i);

            Integer mergedFirstRow = titleLineNum;
            List<T> childrenList = getChildrenList(vo, childrenListField);

            if (U.isEmpty(childrenList)) {
                childrenListField.setAccessible(true);
                Type t = childrenListField.getGenericType();
                if (t instanceof ParameterizedType) {
                    ParameterizedType pt = (ParameterizedType) t;
                    Class<?> clz = (Class) pt.getActualTypeArguments()[0];
                    T o = (T)clz.newInstance();
                    childrenList.add(o);
                }
            }


            //填充行(以孩子的大小填充行)
            for (int k = 0; k < childrenList.size(); k++) {
                Row row = this.sheet.createRow(titleLineNum);
                exportTotalRow++;
                titleLineNum++;

                //填充列
                T o = (T) childrenList.get(k);

                List<Field> childrenExcelField = new ArrayList<>();
                Field[] childrenField = o.getClass().getDeclaredFields();
                for (Field field : childrenField) {
                    Excel attr = field.getAnnotation(Excel.class);
                    if (U.notEmpty(attr)){
                        childrenExcelField.add(field);
                    }
                }


                //子列填充
                for (int j = 0; j < childrenExcelField.size(); j++) {
                    Field field = childrenExcelField.get(j);
                    field.setAccessible(true);
                    Object fieldValue = field.get(o);
                    Cell cell = row.createCell(sortField.size() + j);
                    cell.setCellStyle(this.styles.get("data"));
                    Excel attr = field.getAnnotation(Excel.class);
                    if (U.notEmpty(attr)) {
                        row.setHeight((short) (attr.height() * 20));
                    }
                    setCellVo(o, fieldValue,attr, cell);

                }

                //父列填充
                for (int j = 0; j < sortField.size(); j++) {
                    fillParentCell(sortField, row, j, vo);
                    //子数据是最后一行的时候合并
                    if (childrenList.size() > 1 && k == childrenList.size() - 1) {
                        CellRangeAddress region = new CellRangeAddress(mergedFirstRow, mergedFirstRow + childrenList.size() - 1, j, j);
                        //addMergedRegion()-》安全耗时
                        sheet.addMergedRegionUnsafe(region);
                    }
                }
            }

        }
    }



    private void fillParentCell(List<ExportField> sortField, Row row, int j, T vo) throws Exception {
        Field field = sortField.get(j).field;
        field.setAccessible(true);
        Object fieldValue = field.get(vo);
        Cell cell = row.createCell(j);
        cell.setCellStyle(this.styles.get("data"));
        Excel attr = field.getAnnotation(Excel.class);
        if (U.notEmpty(attr)) {
            row.setHeight((short) (attr.height() * 20));
        }
        setCellVo(vo, fieldValue, attr, cell);
    }




    private Field getChildrenListField(Class<T> t) {
        Field[] declaredFields = t.getDeclaredFields();
        for (Field field : declaredFields) {
            Excel attr = field.getAnnotation(Excel.class);
            if (attr!=null && attr.hasChildrenList()) {
                return field;
            }
        }
        return null;
    }




    private List<ExportField> getSortField(Class<?> clazz) {
        List<ExportField> exportFieldList = new ArrayList<>();
        Field[] declaredFields = clazz.getDeclaredFields();
        for (Field field : declaredFields) {
            if (!field.isAnnotationPresent(Excel.class)) {
                continue;
            }
            Excel attr = field.getAnnotation(Excel.class);
            if (attr.hasChildren() || attr.hasChildrenList()) {
                continue;
            }
            exportFieldList.add(new ExportField(field, attr.sort(), attr.name()));
        }
        return exportFieldList.stream().sorted(Comparator.comparing(ExportField::getSort)).collect(Collectors.toList());
    }




    @SneakyThrows
    private List<T> getChildrenList(T vo, Field field) {
        field.setAccessible(true);
        String s = JSON.toJSONString(field.get(vo));
        Type t = field.getGenericType();
        if (t instanceof ParameterizedType) {
            ParameterizedType pt = (ParameterizedType) t;
            Class<T> clz = (Class) pt.getActualTypeArguments()[0];
            return JSON.parseArray(s, clz);
        }
        return null;
    }






    @Data
    @AllArgsConstructor
    class ExportField {
        private Field field;
        private int sort;
        private String name;
    }



}
